package com.xawx.hch.finances.service;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.xawx.hch.finances.bean.MoneyAnnalBean;
import com.xawx.hch.finances.db.DatabaseHelper;
/**
 * Title: LoginActivity
 * Description: ��֧�����ɾ�Ĳ�ķ�����
 * Copyright: Copyright (c) 2012
 * Company: Xawx Teachnology
 * CreateTime: Mar 6, 2012 10:40:36 AM
 * 
 * @author �δ�
 * @version %A%, %B%
 * @since JDK1.6
 */
public class MoneyService {
	private DatabaseHelper databaseHelper;
	private SQLiteDatabase db;

	/**
	 * ������ݿ�
	 */
	public MoneyService(Context context) {
		databaseHelper = new DatabaseHelper(context,
				MoneyAnnalBean.TABLE_ANNAL_CREATE);
	}

	/**
	 * ����ݿ�
	 */
	public void open() {
		System.out.println("open");
		db = databaseHelper.getWritableDatabase();
	}

	/**
	 * �ر���ݿ�
	 */
	public void close() {
		db.close();
	}

	/**
	 * ������
	 */
	public long insert(MoneyAnnalBean moneyAnnalBean) {
		open();
		ContentValues contentValues = new ContentValues();
		contentValues.put(MoneyAnnalBean.MONEY, moneyAnnalBean.getMoney());
		contentValues.put(MoneyAnnalBean.OUTORIN, moneyAnnalBean.getOutOrIn());
		contentValues.put(MoneyAnnalBean.TIME, moneyAnnalBean.getTime());
		contentValues.put(MoneyAnnalBean.ADDRESS, moneyAnnalBean.getAddress());
		contentValues.put(MoneyAnnalBean.OCCURRENCE,
				moneyAnnalBean.getOccurrence());
		contentValues.put(MoneyAnnalBean.REMARK, moneyAnnalBean.getRemark());
		return db.insert(MoneyAnnalBean.TABLE_NAME, MoneyAnnalBean.KEY_ID,
				contentValues);
	}

	/**
	 * ��ѯʱ��ε��ܽ��
	 */
	public double selectSum(String outOrIn, String startTime, String endTime) {
		open();
		String sql = "select sum(" + MoneyAnnalBean.MONEY
				+ ") money_sum from money_annal where " + MoneyAnnalBean.TIME
				+ " between '" + startTime + "' and '" + endTime
				+ "' and outOrIn='" + outOrIn + "'";
		System.out.println("sumsql:" + sql);
		Cursor cursor = db.rawQuery(sql, null);
		System.out.println("cursor:" + cursor);
		double moneySum = 0.0;
		if (cursor.moveToFirst()) {
			moneySum = cursor.getDouble(cursor.getColumnIndex("money_sum"));
		}
		System.out.println("moneySum:" + moneySum);
		close();
		return moneySum;
	}

	/**
	 * ��ݴ������������sql���
	 */
	public ArrayList<MoneyAnnalBean> select(String name, String startCondition,
			String endCondition) {
		String sql = null;
		if (name.equals("����") || name.equals("֧��")) {
			sql = "select * from " + MoneyAnnalBean.TABLE_NAME + " where "
					+ MoneyAnnalBean.OUTORIN + "='" + startCondition
					+ "' order by " + MoneyAnnalBean.TIME + " desc";
		} else if (name.equals("����")) {
			sql = "select * from " + MoneyAnnalBean.TABLE_NAME + " order by "
					+ MoneyAnnalBean.TIME + " desc";
		} else if (name.equals("���")) {
			sql = "select * from " + MoneyAnnalBean.TABLE_NAME + " where "
					+ MoneyAnnalBean.OCCURRENCE + "='" + startCondition
					+ "' order by " + MoneyAnnalBean.TIME + " desc";
		} else if (name.equals("ʱ��")) {
			sql = "select * from " + MoneyAnnalBean.TABLE_NAME + " where "
					+ MoneyAnnalBean.TIME + " between '" + startCondition
					+ "' and '" + endCondition + "' order by "
					+ MoneyAnnalBean.TIME + " desc";
			// sql
			// ="select * from "+MoneyAnnalBean.TABLE_NAME+" where "+MoneyAnnalBean.TIME+" between '2012-5-1' and '2012-5-8' order by "+MoneyAnnalBean.TIME+" desc";
		} else if (name.equals("���")) {
			sql = "select * from " + MoneyAnnalBean.TABLE_NAME + " where "
					+ MoneyAnnalBean.MONEY + " between " + startCondition
					+ " and " + endCondition + " order by "
					+ MoneyAnnalBean.TIME + " desc";
			// sql="select * from "+MoneyAnnalBean.TABLE_NAME+" where "+MoneyAnnalBean.MONEY+"between "+startCondition+" and "+endCondition+" order by "+MoneyAnnalBean.TIME+" desc";
		}
		System.out.println("sql:" + sql);
		return selectByName(sql);
	}

	/**
	 * ���sql����ѯ���
	 */
	public ArrayList<MoneyAnnalBean> selectByName(String sql) {
		open();
		SQLiteDatabase sqLiteDatabase = databaseHelper.getReadableDatabase();
		ArrayList<MoneyAnnalBean> arrayList = new ArrayList<MoneyAnnalBean>();
		System.out.println("����sql��䣺" + sql);
		Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
		while (cursor.moveToNext()) {
		    System.out.println("cursor.getColumnIndex(MoneyAnnalBean.KEY_ID)"+cursor.getColumnIndex(MoneyAnnalBean.KEY_ID));
		    System.out.println("cursor.getColumnIndex(MoneyAnnalBean.TIME)"+cursor.getColumnIndex(MoneyAnnalBean.TIME));
			int selectId = cursor.getInt(cursor
					.getColumnIndex(MoneyAnnalBean.KEY_ID));
			String selectTime = cursor.getString(cursor
					.getColumnIndex(MoneyAnnalBean.TIME));
			String selectOutOrIn = cursor.getString(cursor
					.getColumnIndex(MoneyAnnalBean.OUTORIN));
			Double selectMoney = cursor.getDouble(cursor
					.getColumnIndex(MoneyAnnalBean.MONEY));
			String selectOccurrence = cursor.getString(cursor
					.getColumnIndex(MoneyAnnalBean.OCCURRENCE));
			String selectAddress = cursor.getString(cursor
					.getColumnIndex(MoneyAnnalBean.ADDRESS));
			String selectRemark = cursor.getString(cursor
					.getColumnIndex(MoneyAnnalBean.REMARK));
			System.out.println("selectTime:" + selectTime);
			System.out.println("selectOutOrIn:" + selectOutOrIn);
			System.out.println("selectMoney:" + selectMoney);
			// ��������ӵ�������
			arrayList.add(new MoneyAnnalBean(selectId, selectTime,
					selectOutOrIn, selectMoney, selectOccurrence,
					selectAddress, selectRemark));
		}
		close();
		sqLiteDatabase.close();
		return arrayList;
	}

	/**
	 * ���id�޸����
	 */
	public void update(MoneyAnnalBean moneyAnnalBean) {
		open();
		db.execSQL(
				"update "
						+ MoneyAnnalBean.TABLE_NAME
						+ " set money=?,outOrIn=?,occurrence=?,time=?,address=?,REMARK=? where id=?",
				new Object[] { moneyAnnalBean.getMoney(),
						moneyAnnalBean.getOutOrIn(),
						moneyAnnalBean.getOccurrence(),
						moneyAnnalBean.getTime(), moneyAnnalBean.getAddress(),
						moneyAnnalBean.getRemark(), moneyAnnalBean.getId() });
		close();
	}

	/**
	 * ���idɾ�����
	 */
	public void delete(int id) {
		open();
		String sql = "delete from money_annal where id=" + id + "";
		System.out.println("deletesql:" + sql);
		db.execSQL(sql);
		close();
	}
}
